package com.sh.data.engine.domain.shims.sqlserver.manager;

import com.sh.data.engine.domain.shims.db.DbOptions;
import com.sh.data.engine.domain.shims.db.GenericDbManager;
import com.sh.data.engine.domain.shims.db.model.FieldInfoDomain;
import com.sh.data.engine.domain.shims.db.model.PreviewDataDomain;
import com.sh.data.engine.domain.shims.db.model.TableInfoDomain;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;

import java.sql.*;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Set;

/**
 * @author: mengzheng.mhc
 * @date: 2024/6/7 9:17
 */
public class SqlServerManager extends GenericDbManager {

    public SqlServerManager(DbOptions options) {
        super(options);
    }

    static {
        String driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("Could not load db driver class: " + driverClass);
        }
    }

    @Override
    public String getQueryVersionSql() {
        return "SELECT CONVERT(varchar(100), SERVERPROPERTY('productversion'))";
    }

    @Override
    public String getQuerySchemaListSql() {
        return "SELECT s.name FROM sys.schemas s INNER JOIN sys.sysusers u ON u.uid = s.principal_id where u.name in ('dbo','guest')";
    }

    @Override
    public boolean alterFieldDesc(
        String dbName, String tableName, String fieldName, String fieldDes, boolean isPartition)
        throws SQLException {
        throw new SQLException("暂未实现");
    }

    @Override
    public List<TableInfoDomain> getTableList(String dbName) throws SQLException {
        String sql =
            "SELECT DISTINCT "
                + "s.name schema_name, "
                + "d.name object_name, "
                + "d.id, "
                + "d.xtype, "
                + "f.value  "
                + "FROM "
                + "syscolumns a "
                + "LEFT JOIN systypes b ON a.xusertype= b.xusertype "
                + "INNER JOIN sysobjects d ON a.id= d.id  "
                + "AND d.xtype = 'U'  "
                + "AND d.name<> 'dtproperties' "
                + "left join sys.schemas s on d.uid = s.schema_id "
                + "LEFT JOIN syscomments e ON a.cdefault= e.id "
                + "LEFT JOIN sys.extended_properties g ON a.id= g.major_id  "
                + "AND a.colid= g.minor_id "
                + "LEFT JOIN sys.extended_properties f ON d.id= f.major_id  "
                + "AND f.minor_id= 0";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection connection = getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs != null && rs.next()) {
                final TableInfoDomain tableDomain = getTableDomain(dbName, rs);
                list.add(tableDomain);
            }
        }
        return list;
    }

    @Override
    public List<TableInfoDomain> getTableList(String dbName, String schema) throws SQLException {
        String sql =
            "SELECT user_name(uid) AS [schema_name], name AS table_name"
                + " FROM "
                + dbName
                + "..sysobjects"
                + " WHERE type IN ('U')"
                + " and user_name(uid) = '?'"
                + " ORDER BY [schema_name], name";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection connection = getConnection();
             PreparedStatement pstmt = createPreparedStatement(connection, sql, schema);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs != null && rs.next()) {
                final TableInfoDomain tableDomain = getTableDomain(dbName, rs);
                list.add(tableDomain);
            }
        }
        return list;
    }

    private static TableInfoDomain getTableDomain(String dbName, ResultSet rs) throws SQLException {
        ResultSetMetaData meta = rs.getMetaData();
        Set<String> set = new HashSet<>();
        for (int index = 1; index <= meta.getColumnCount(); index++) {
            set.add(meta.getColumnLabel(index));
        }
        TableInfoDomain entity = new TableInfoDomain();
        entity.setDbName(dbName);
        String schemaName = rs.getString("schema_name");
        String tableName = rs.getString("table_name");
        entity.setSchema(schemaName);
        entity.setTableName(tableName);
        //    entity.setTableComment(rs.getString("value"));
        //
        //    final String objectType = rs.getString("xtype");

        //    TableInfoDomain.TblType tblType = null;
        //    switch (objectType) {
        //      case "U":
        //        tblType = TableInfoDomain.TblType.MANAGED_TABLE;
        //        break;
        //      case "V":
        //        tblType = TableInfoDomain.TblType.VIEW;
        //        break;
        //    }
        //    entity.setTblType(tblType);

        return entity;
    }

    @Override
    public List<TableInfoDomain> getViewList(String dbName) throws SQLException {
        String sql =
            "SELECT DISTINCT "
                + "s.name schema_name, "
                + "d.name object_name, "
                + "d.id, "
                + "d.xtype, "
                + "f.value  "
                + "FROM "
                + "syscolumns a "
                + "LEFT JOIN systypes b ON a.xusertype= b.xusertype "
                + "INNER JOIN sysobjects d ON a.id= d.id  "
                + "AND d.xtype = 'V' "
                + "AND d.name<> 'dtproperties' "
                + "left join sys.schemas s on d.uid = s.schema_id "
                + "LEFT JOIN syscomments e ON a.cdefault= e.id "
                + "LEFT JOIN sys.extended_properties g ON a.id= g.major_id  "
                + "AND a.colid= g.minor_id "
                + "LEFT JOIN sys.extended_properties f ON d.id= f.major_id  "
                + "AND f.minor_id= 0";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection connection = getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs != null && rs.next()) {
                final TableInfoDomain tableDomain = getTableDomain(dbName, rs);
                list.add(tableDomain);
            }
        }
        return list;
    }

    @Override
    public List<TableInfoDomain> getViewList(String dbName, String schema) throws SQLException {
        String sql =
            "SELECT DISTINCT "
                + "s.name schema_name, "
                + "d.name object_name, "
                + "d.id, "
                + "d.xtype, "
                + "f.value  "
                + "FROM "
                + "syscolumns a "
                + "LEFT JOIN systypes b ON a.xusertype= b.xusertype "
                + "INNER JOIN sysobjects d ON a.id= d.id  "
                + "AND d.xtype = 'V' "
                + "AND d.name<> 'dtproperties' "
                + "left join sys.schemas s on d.uid = s.schema_id "
                + "LEFT JOIN syscomments e ON a.cdefault= e.id "
                + "LEFT JOIN sys.extended_properties g ON a.id= g.major_id  "
                + "AND a.colid= g.minor_id "
                + "LEFT JOIN sys.extended_properties f ON d.id= f.major_id  "
                + "AND f.minor_id= 0 "
                + "where s.name = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection connection = getConnection();
             PreparedStatement pstmt = createPreparedStatement(connection, sql, schema);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs != null && rs.next()) {
                final TableInfoDomain tableDomain = getTableDomain(dbName, rs);
                list.add(tableDomain);
            }
        }
        return list;
    }

    @Override
    public List<TableInfoDomain> getTableAndViewList(String dbName) throws SQLException {
        String sql =
            "SELECT user_name(uid) AS [schema_name], name AS table_name FROM "
                + dbName
                + "..sysobjects"
                + " WHERE type IN ('U','V')"
                + " ORDER BY [schema_name], name";
        //        "SELECT DISTINCT "
        //            + "s.name schema_name, "
        //            + "d.name object_name, "
        //            + "d.id, "
        //            + "d.xtype, "
        //            + "f.value  "
        //            + "FROM "
        //            + "syscolumns a "
        //            + "LEFT JOIN systypes b ON a.xusertype= b.xusertype "
        //            + "INNER JOIN sysobjects d ON a.id= d.id  "
        //            + "AND d.xtype IN ( 'U', 'V' )  "
        //            + "AND d.name<> 'dtproperties' "
        //            + "left join sys.schemas s on d.uid = s.schema_id "
        //            + "LEFT JOIN syscomments e ON a.cdefault= e.id "
        //            + "LEFT JOIN sys.extended_properties g ON a.id= g.major_id  "
        //            + "AND a.colid= g.minor_id "
        //            + "LEFT JOIN sys.extended_properties f ON d.id= f.major_id  "
        //            + "AND f.minor_id= 0";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection connection = getConnection();
             PreparedStatement pstmt = connection.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs != null && rs.next()) {
                final TableInfoDomain tableDomain = getTableDomain(dbName, rs);
                list.add(tableDomain);
            }
        }
        return list;
    }

    @Override
    public List<FieldInfoDomain> getFieldList(String dbName, String schema, String tableName)
        throws SQLException {
        List<FieldInfoDomain> list = new LinkedList<>();


        String sql = "SELECT \n"
            + "    TABLE_SCHEMA AS [Schema],\n"
            + "    TABLE_NAME AS [Table],\n"
            + "    COLUMN_NAME AS [COLUMN_NAME],\n"
            + "    NUMERIC_PRECISION AS [NUMERIC_PRECISION],\n"
            + "    NUMERIC_SCALE AS [NUMERIC_SCALE],\n"
            + "    DATA_TYPE AS [TYPE_NAME],\n"
            + "    CHARACTER_MAXIMUM_LENGTH AS [COLUMN_SIZE],\n"
            + "    IS_NULLABLE AS [IsNullable],\n"
            + "    COLUMN_DEFAULT AS [DefaultValue],\n"
            + "    CASE WHEN COLUMNPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') = 1 THEN 'Yes' ELSE 'No' END AS [IsIdentity],\n"
            + "    CASE WHEN EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku ON tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME AND tc.TABLE_NAME = ku.TABLE_NAME AND tc.TABLE_SCHEMA = ku.TABLE_SCHEMA WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY' AND ku.COLUMN_NAME = c.COLUMN_NAME AND ku.TABLE_NAME = c.TABLE_NAME AND ku.TABLE_SCHEMA = c.TABLE_SCHEMA) THEN 'Yes' ELSE 'No' END AS [IsPrimaryKey]\n"
            + "FROM \n"
            + "    INFORMATION_SCHEMA.COLUMNS c\n"
            + "WHERE \n"
            + "    TABLE_SCHEMA = ? \n"
            + "    AND TABLE_NAME = ? \n"
            + "ORDER BY \n"
            + "    [Schema], \n"
            + "    [Table], \n"
            + "    ORDINAL_POSITION\n";

        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = createPreparedStatement(connection, sql, schema, tableName);
             ResultSet rs = preparedStatement.executeQuery()) {
            while (rs != null && rs.next()) {
                String typeName = rs.getString("TYPE_NAME");
                String columnName = rs.getString("COLUMN_NAME");
                int columnSize = rs.getInt("COLUMN_SIZE");
                // 字段是否可为NULL，0-NO  1-YES
                String nullableStr = rs.getString("IsNullable");
                //是否为主键
                String isPrimaryKeyStr = rs.getString("IsPrimaryKey");

                boolean nullable = true;
                boolean isPrimaryKey = false;
                if (StringUtils.isNotBlank(nullableStr) && nullableStr.equalsIgnoreCase("no")) {
                    nullable = false;
                }
                if (StringUtils.isNotBlank(isPrimaryKeyStr) && isPrimaryKeyStr.equalsIgnoreCase("yes")) {
                    isPrimaryKey = true;
                }

                if (typeName.equalsIgnoreCase("decimal")) {
                    int numericPrecision = rs.getInt("NUMERIC_PRECISION");
                    int numericScale = rs.getInt("NUMERIC_SCALE");
                    typeName = typeName + "(" + numericPrecision + "," + numericScale + ")";
                }


                if (typeName.equalsIgnoreCase("varchar") || typeName.equalsIgnoreCase("char")) {
                    typeName = typeName + "(" + columnSize + ")";
                }

                FieldInfoDomain fieldInfo =
                    FieldInfoDomain.builder()
                        .fieldName(columnName)
                        .fieldType(typeName)
                        .columnSize(columnSize)
                        .isNullable(nullable)
                        .isPk(isPrimaryKey)
                        .build();
                list.add(fieldInfo);
            }
        }
        if (CollectionUtils.isEmpty(list)) {
            return list;
        }

        return list;
    }


    @Override
    public List<TableInfoDomain> getTableAndViewList(String dbName, String schema)
        throws SQLException {
        if (StringUtils.isBlank(schema)) {
            return getTableAndViewList(dbName);
        }
        String sql =
            "SELECT user_name(uid) AS [schema_name], name AS table_name FROM "
                + dbName
                + "..sysobjects"
                + " WHERE type IN ('U','V')"
                + " and user_name(uid) = ? "
                + " ORDER BY [schema_name], name";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection connection = getConnection();
             PreparedStatement pstmt = createPreparedStatement(connection, sql, schema);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs != null && rs.next()) {
                final TableInfoDomain tableDomain = getTableDomain(dbName, rs);
                list.add(tableDomain);
            }
        }
        return list;
    }

    @Override
    public TableInfoDomain getTableInfoByTableName(String dbName, String tableName)
        throws SQLException {
        if (StringUtils.isNotBlank(dbOptions.getSchema())) {
            return getTableInfoByTableName(dbName, dbOptions.getSchema(), tableName);
        }

        String sql =
            "SELECT DISTINCT "
                + "s.name schema_name, "
                + "d.name object_name, "
                + "d.id, "
                + "d.xtype, "
                + "f.value  "
                + "FROM "
                + "syscolumns a "
                + "LEFT JOIN systypes b ON a.xusertype= b.xusertype "
                + "INNER JOIN sysobjects d ON a.id= d.id  "
                + "AND d.xtype IN ( 'U', 'V' )  "
                + "AND d.name<> 'dtproperties' "
                + "left join sys.schemas s on d.uid = s.schema_id "
                + "LEFT JOIN syscomments e ON a.cdefault= e.id "
                + "LEFT JOIN sys.extended_properties g ON a.id= g.major_id  "
                + "AND a.colid= g.minor_id "
                + "LEFT JOIN sys.extended_properties f ON d.id= f.major_id  "
                + "AND f.minor_id= 0 "
                + "where d.name = ? ";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection connection = getConnection();
             PreparedStatement pstmt = createPreparedStatement(connection, sql, tableName);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs != null && rs.next()) {
                final TableInfoDomain tableDomain = getTableDomain(dbName, rs);
                list.add(tableDomain);
            }
        }
        return list.isEmpty() ? null : list.get(0);
    }

    @Override
    public TableInfoDomain getTableInfoByTableName(String dbName, String schema, String tableName)
        throws SQLException {
        String sql =
            "SELECT user_name(uid) AS [schema_name], name AS table_name FROM "
                + dbName
                + "..sysobjects"
                + " WHERE type IN ('U','V')"
                + " and user_name(uid) = ? "
                + " and name = ?"
                + " ORDER BY [schema_name], name";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection connection = getConnection();
             PreparedStatement pstmt = createPreparedStatement(connection, sql, schema, tableName);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs != null && rs.next()) {
                final TableInfoDomain tableDomain = getTableDomain(dbName, rs);
                list.add(tableDomain);
            }
        }
        return list.isEmpty() ? null : list.get(0);
    }

    /**
     * sqlserver的表名要用[]括起来
     *
     * @param tableName
     * @return
     */
    @Override
    public String getPreviewDataSql(String tableName) {
        if (!StringUtils.startsWith(tableName, "[") || !StringUtils.endsWith(tableName, "]")) {
            tableName = String.format("[%s]", tableName);
        }
        String sql = "SELECT TOP 10 * FROM " + tableName;
        return sql;
    }

    @Override
    public PreviewDataDomain previewData(String schema, String tableName) throws SQLException {
        return super.previewData(String.format("[%s].[%s]", schema, tableName));
    }

    public static void main(String[] args) throws SQLException {
        DbOptions dbOptions = new DbOptions();
        dbOptions.setUrl("jdbc:sqlserver://10.88.36.79:1433;DataBaseName=test");
        dbOptions.setUsername("sa");
        dbOptions.setPassword("1234@Qwer");

        SqlServerManager manager = new SqlServerManager(dbOptions);
        final List<String> schemaList = manager.getSchemaList();
        schemaList.forEach(System.out::println);

        final String dbVersion = manager.getDbVersion();
        System.out.println(dbVersion);

        List<TableInfoDomain> tableList = manager.getTableAndViewList("fuchen", "dbo");
        TableInfoDomain tableInfoByTableName =
            manager.getTableInfoByTableName("fuchen", "dbo", "test_fuchen");
        List<FieldInfoDomain> fieldList = manager.getFieldList("fuchen", "dbo", "test_fuchen");
        final List<TableInfoDomain> tableAndViewList = manager.getTableAndViewList("");
        tableAndViewList.forEach(System.out::println);
        final PreviewDataDomain previewDataDomain = manager.previewData("mttest14[");
        System.out.println(previewDataDomain);
    }
}
